IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageGetApprovedRevisions]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageGetApprovedRevisions]
GO
create procedure [dbo].[cms_sp_PageGetApprovedRevisions]
(
@SiteGroupId int = null,
@SiteId int = null,
@PageTypeParameter nvarchar(400)
)
as
declare @result table
(
	PageId int
)
insert into @result
select Pages.PageId from dbo.Pages
inner join dbo.EntityRevisions
	on EntityRevisions.EntityId = Pages.PageId and 
	   EntityRevisions.EntityTypeParameter = @PageTypeParameter
where ((Pages.SiteGroupId is null and Pages.SiteId is null) or 
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId is null) or
	(Pages.SiteGroupId = @SiteGroupId and Pages.SiteId = @SiteId) or
	(@SiteId is null and @SiteGroupId is null)) and
	EntityRevisions.IsLatestCompletedRevision = 1 and
	EntityRevisions.WorkflowId is not null
select 
	Pages.PageId,
	Pages.SiteId,
	Pages.SiteGroupId,
	Pages.SystemLanguageId,
	Pages.PublicPageId,
	Pages.PageName,
	Pages.PageUrl,
	Pages.PageTypeKey,
	Pages.PageBehaviourTypeKey,
	Pages.UseFileCache,
	Pages.CreatedBy,
	Pages.DateCreated,
	Pages.LastUpdatedBy,
	Pages.DateLastUpdated,
	Pages.PageKey
from dbo.Pages 
inner join @result as tmp
	on tmp.PageId = Pages.PageId 	
select 
	tmp.PageId,
	EntityMetas.EntityMetaId,
	EntityMetas.EntityId,
	EntityMetas.EntityMetaValue,
	EntityMetaFieldLinks.EntityMetaFieldLinkId,
	EntityMetaFields.EntityMetaFieldId,
	EntityMetaFields.MetaFieldName, 
	EntityMetaFields.MetaFieldType,
	EntityMetaFields.IsSystem	
from dbo.EntityMetaFields
inner join dbo.EntityMetaFieldLinks
	on EntityMetaFieldLinks.EntityMetaFieldId = EntityMetaFields.EntityMetaFieldId and 
	   EntityMetaFieldLinks.EntityTypeParameter = @PageTypeParameter
inner join dbo.EntityMetas
	on EntityMetas.EntityMetaFieldLinkId = EntityMetaFieldLinks.EntityMetaFieldLinkId
inner join @result as tmp
	on tmp.PageId = EntityMetas.EntityId
GO